<?php

include ("php_excel_utf8_allinone.php");
require_once('connections/mysqlDB.php');
$dbConn = new mysqli(HOST,USER,PWD,DB);
$dbConn->query("SET NAMES 'utf8'");

// this is how you create the workbook object.
$workbook = new Spreadsheet_Excel_Writer();


// sending HTTP headers so that the clients will be prompted to open the Excel file
// do a google if you want to save the file somewhere in your server instead
$workbook->send('reports.xls');

// This is the magic - to enable BIFF8! Always include this line if you plan to pump UTF-8 values into the cells
$workbook->setVersion(8);

function create_excel_spreadsheet($sql,$name,$dbConn,$workbook) {
	$worksheet =& $workbook->addWorksheet($name); 
	$worksheet->setInputEncoding('UTF-8');

	$result = mysqli_query($dbConn,$sql);

	if($result) { 
		$cnt=0;
		$fields = mysqli_fetch_fields($result);
		foreach($fields as $fi => $f) 
			{
  				$worksheet->write(1,$cnt++,$f->name);
			}
	}

	$rowcount=1;
	while ($abc = mysqli_fetch_row($result)) {
        $rowcount++;
        for ($k=0; $k<count($abc); $k++){
			$worksheet->write($rowcount,$k,$abc[$k]);
        }
	}
}

// get registration list
// Creating a worksheet
$sql1="SELECT b.conference_id, b.rid, a.cname, a.first_name, a.last_name, a.gender, 
(select description from code where abbrev = relationship) as relationship,
(select description from code where abbrev = groups) as groups,
 meal1 as 'Dinner 1', meal2 as 'Lunch 2', meal3 as 'Dinner 2', meal4 as 'Lunch 3', meal5 as 'Dinner 3',
  meal6 as 'Lunch 4', meal7 as 'Dinner 4', meal8 as 'Lunch 5'
FROM registration_info a, registration b, conference c, registrant d
WHERE c.conference_id = '2009Summer' 
and c.conference_id = b.conference_id 
and a.rid = b.rid
and b.regid = d.regid
order by b.conference_id, b.rid";

create_excel_spreadsheet($sql1,'Registration Detail List',$dbConn,$workbook);


$sql="SELECT b.rid, d.cname as '中文姓名',  d.first_name as 'First Name', d.last_name as 'Last Name', a.pastoral_staff as 'Pastor',
count(*) as 'Total Registration', sum(meal2)+sum(meal4)+sum(meal6)+sum(meal8) as 'Lunch',
sum(meal1)+sum(meal3)+sum(meal5)+sum(meal7) as 'Dinner'
FROM registrant a, registration b, conference c, registration_info d
WHERE c.conference_id='2009Summer' and a.regid = b.regid
AND b.conference_id = c.conference_id and b.rid = d.rid 
Group by a.cname, b.rid, b.conference_id order by b.rid";

create_excel_spreadsheet($sql,'Registration List',$dbConn,$workbook);

// Get pastor & spouse list


$sql2="SELECT  b.rid,d.cname as '中文姓名', d.first_name as 'First Name', d.last_name as 'Last Name', d.pastoral_staff as 'Pastor', 
(select description from code where abbrev = relationship) as relationship,
session1,session2,session3,session4,session5,session6,session7,session8,session9,session10,session11
FROM registrant a, registration b, conference c, registration_info d
WHERE c.conference_id='2009Summer' and a.regid = b.regid
AND b.conference_id = c.conference_id
and b.rid = d.rid 
and a.pastoral_staff in ('P','Y','M')
order by b.rid, relationship asc, d.last_name, d.first_name ;";

create_excel_spreadsheet($sql2,'Pastor & Spouse List',$dbConn, $workbook);

$sql3 ="SELECT b.conference_id, count(*),
sum(meal1) as 'Dinner 1' ,sum(meal2) as 'Lunch 2' ,
sum(meal3) as 'Dinner 2',sum(meal4) as 'Lunch 3', sum(meal5) as 'Dinner 3',
sum(meal6) as 'Lunch 4',sum(meal7) as 'Dinner 4' ,sum(meal8) as 'Lunch 5'
FROM registrant a, registration b, conference c,  registration_info d
WHERE c.conference_id='2009Summer' and a.regid = b.regid 
AND b.conference_id = c.conference_id
and b.rid = d.rid
Group by  b.conference_id";

create_excel_spreadsheet($sql3,'Conference Meals',$dbConn, $workbook);

$sql="SELECT b.conference_id,
case when (groups >=218) then 'Adult'
when (groups between 215 and 216) then 'Young Adult'
when (groups between 209 and 214 ) then 'Youth'
when (groups between 203 and 208) then 'Children'
when (groups = 202 ) then 'Toddle'
else 'Baby'
END as 'Classification', count(*)
FROM registration_info a, registration b, conference c, registrant d
WHERE c.conference_id = '2009Summer'
and c.conference_id = b.conference_id
and a.rid = b.rid
and b.regid = d.regid
group by b.conference_id,  Classification";

create_excel_spreadsheet($sql,'Category',$dbConn, $workbook);

$sql="SELECT b.conference_id, (select description from code where abbrev = country) as country,
 count(*)
FROM registration_info a, registration b, conference c, registrant d
WHERE c.conference_id = '2009Summer'
and c.conference_id = b.conference_id
and a.rid = b.rid
and b.regid = d.regid
group by b.conference_id, d.country
order by b.conference_id, d.country";

create_excel_spreadsheet($sql,'Country Breakdown',$dbConn, $workbook);

$sql="SELECT b.conference_id, (select description from code where abbrev = state) as state,
 count(*)
FROM registration_info a, registration b, conference c, registrant d
WHERE c.conference_id = '2009Summer'
and c.conference_id = b.conference_id
and a.rid = b.rid
and b.regid = d.regid and country = 'USA'
group by b.conference_id, state
order by b.conference_id, state";

create_excel_spreadsheet($sql,'State Breakdown',$dbConn, $workbook);

$workbook->close();

//mysqli_free_result($result);
//	$result->close();
	$dbConn->close();

/*

// Creating a worksheet
$worksheet =& $workbook->addWorksheet('Search Transaction'); // lovely sheet name

// Another magic - you must set this value to the encoding of your values
$worksheet->setInputEncoding('UTF-8');

$worksheet->write(0,0,"English Name");
$worksheet->write(0,1,"Chinese Name");
$worksheet->write(0,2,"Age");

$worksheet->write(1,0,"Eddy Wong");
$worksheet->write(1,1,"æ±ªå¿ è€€"); // UTF-8 text!
$worksheet->write(1,2,"20");


// Let's send the file
$workbook->close();


*/
?>
